package dao.room;

import domain.Page;
import domain.Room;
import domain.User;
import util.DButils;
import util.EncryptUtil;
import util.StringUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class RoomDaoImpl implements RoomDao {
    /**
     * 验证username是否重复
     * @param username
     * @return boolean
     * @author 夏添
     * @date 2020/7/15 21:33
     */
    @Override
    public boolean ValidateName(String username) throws  Exception{
        String sql="select count(*) as count from room where Username='"+username+"'";
        Connection con=DButils.getConn();
        int count=1;
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            rs.next();
            count = rs.getInt("count");
            DButils.close(rs,st,con);

        if(count==0)
            return true;
        else
            return false;
    }

    /**
     * room信息注册
     * @param username,password,phone,community_id,housernum,ownername,housecard_img
     * @return int
     * @author 夏添
     * @date 2020/7/15 21:33
     */
    @Override
    public int RegisteredRoom(String username,String password,int community_id,String housernum,String ownername,String phone,String housecard_img) throws Exception{
        String sql = "insert into room (Username,Password,Community_id,Housernum,Ownername,Phone,Housecard_img,State) values('"+
                username+"','"+password+"',"+community_id+",'"+housernum+"','"+ownername+"','"+phone+"','"+housecard_img+"',0)";
        System.out.println(sql);
        Connection con = DButils.getConn();
        int i =0;
        Statement st = con.createStatement();
        i = st.executeUpdate(sql);
        return i;
    }
    /**
     * 前台登录验证
     * @param username,password
     * @return room
     * @author 夏添
     * @date 2020/7/18 21:33
     */
    @Override
    public Room verify(String username , String password) throws Exception {
        int Room_id = 0;
        String Username =null;
        String Password=null;
        int Community_id=0;
        String Ownername = null;
        String Phone = null;
        String Housernum = null;
        String Housecard_img = null;
        int State = 0;
        String Community_name=null;
        String Community_info=null;
        Room room = new Room();
        String sql = "select * from room  join community on room.Community_id = community.Community_id where room.Username = '"+username+"'";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            Room_id = rs.getInt("Room_id");
            Username = rs.getString("Username");
            Password = rs.getString("Password");
            Community_id = rs.getInt("Community_id");
            Ownername = rs.getString("Ownername");
            Housernum = rs.getString("Housernum");
            Phone = rs.getString("Phone");
            Housecard_img = rs.getString("Housecard_img");
            State = rs.getInt("State");
            Community_name = rs.getString("Community_name");
            Community_info = rs.getString("Community_info");
            if (username.equals(Username) && password.equals(Password)) {
                room = new Room();
                room.setRoom_id(Room_id);
                room.setUsername(Username);
                room.setCommunity(Community_id);
                room.setOwnername(Ownername);
                room.setHousernum(Housernum);
                room.setPhone(Phone);
                room.setHousecard_img(Housecard_img);
                room.setState(State);
                room.setCommunity_name(Community_name);
                room.setCommunity_info(Community_info);
            }
        }DButils.close(rs,st,con);
        return room;
    }

    /**
     * 住房信息检索
     * @param page
     * @return ArrayList
     * @author 夏添
     * @date 2020/7/20 20:16
     */
    @Override
    public ArrayList<Room> findByPage(Page page,String view_power) throws Exception{
        ArrayList<Room> list=new ArrayList<Room>();
        if (view_power==null){
            return list;
        }
        else {
            view_power = view_power.substring(0,view_power.length()-1);
            String sql = "select * from room join community on room.Community_id = community.Community_id where room.Community_id in ("+view_power+") limit " + page.getBeginIndex() + ", " + page.getEveryPage();
            Connection con = DButils.getConn();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                Room room = new Room();
                room.setRoom_id(rs.getInt("Room_id"));
                room.setUsername(rs.getString("Username"));
                room.setCommunity(rs.getInt("Community_id"));
                room.setOwnername(rs.getString("Ownername"));
                room.setHousernum(rs.getString("Housernum"));
                room.setPhone(rs.getString("Phone"));
                room.setHousecard_img(rs.getString("Housecard_img"));
                room.setState(rs.getInt("State"));
                room.setCommunity_name(rs.getString("Community_name"));
                list.add(room);
            }
            DButils.close(rs, st, con);
            return list;
        }
    }

    /**
     * 住房信息搜索
     * @param page,community_id,query,view_power
     * @return ArrayList
     * @author 夏添
     * @date 2020/7/20 20:32
     */
    @Override
    public ArrayList<Room> QueryRoom(Page page,int community_id,String query,String view_power) throws  Exception{
        String sql = null;
        if (community_id==0&&query==null) {
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select * from room join community on room.Community_id = community.Community_id where room.Community_id in ("+view_power+") limit " + page.getBeginIndex() + ", " + page.getEveryPage();
        }
        if(community_id != 0 && query==null){
            sql = "select * from room join community on room.Community_id = community.Community_id where  room.Community_id="+community_id+" limit " + page.getBeginIndex() + ", " + page.getEveryPage();

        }
        if(community_id !=0 && query !=null){
            sql = "select * from room join community on room.Community_id = community.Community_id where (Username LIKE '%"+query
                    +"%' or Ownername LIKE '%"+query+"%' or Housernum LIKE '%"+query+"%' or Phone LIKE '%"+query+"%' ) and room.Community_id="+community_id+" limit " + page.getBeginIndex() + ", " + page.getEveryPage();
        }if(community_id ==0 && query !=null){
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select * from room join community on room.Community_id = community.Community_id where (Username LIKE '%"+query
                    +"%' or Ownername LIKE '%"+query+"%' or Housernum LIKE '%"+query+"%' or Phone LIKE '%"+query+"%' ) and room.Community_id in ("+view_power+" ) limit " + page.getBeginIndex() + ", " + page.getEveryPage();
        }
        System.out.println("查询的sql语句是"+sql);
        ArrayList<Room> list=new ArrayList<Room>();
        Connection con = DButils.getConn();
        Statement st=con.createStatement();
        ResultSet rs=st.executeQuery(sql);
        while (rs.next()){
            Room room =new Room();
            room.setRoom_id(rs.getInt("Room_id"));
            room.setUsername(rs.getString("Username"));
            room.setCommunity(rs.getInt("Community_id"));
            room.setOwnername(rs.getString("Ownername"));
            room.setHousernum(rs.getString("Housernum"));
            room.setPhone(rs.getString("Phone"));
            room.setHousecard_img(rs.getString("Housecard_img"));
            room.setState(rs.getInt("State"));
            room.setCommunity_name(rs.getString("Community_name"));
            list.add(room);
        }
        DButils.close(rs,st,con);
        return  list;
    }

    /**
     * 更新住房信息
     * @param room_id,phone,community_id,housernum,ownername,housecard_img
     * @return int
     * @author 夏添
     * @date 2020/7/20 21:19
     */
    @Override
    public  int UpdateInfo(int room_id,String phone,String ownername,String hosecard_img) throws Exception{
        String sql =null;
        if (hosecard_img == null)
            sql = "update room set Phone='"+phone+"',Ownername='"+ownername+"',State=0 where Room_id = "+room_id;
        else
            sql = "update room set Phone='"+phone+"',Ownername='"+ownername+"',Housecard_img='"+hosecard_img+"',State=0 where Room_id = "+room_id;
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i =st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 审核住房信息
     * @param room_id
     * @return int
     * @author 夏添
     * @date 2020/7/20 21:24
     */
    @Override
    public int StateYes(int room_id) throws  Exception{
        String sql = "update room set State=1 where Room_id ="+room_id;
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 拒绝住房信息
     * @param room_id
     * @return int
     * @author 夏添
     * @date 2020/7/20 21:26
     */
    @Override
    public int StateNo(int room_id) throws  Exception{
        String sql = "update room set State=2 where Room_id ="+room_id;
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 获取所有允许查看的住房数据总数
     * @param view_power
     * @return int
     * @author 夏天
     * @date 2020/7/21 15:41
     */
    @Override
    public int getCount(String view_power) throws Exception{
        view_power = view_power.substring(0,view_power.length()-1);
        String sql = "select count(*) as count from room where Community_id in ("+view_power+")";
        System.out.println("获取住房数量的sql为"+sql);
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int i = rs.getInt("count");
        DButils.close(rs,st,con);
        return i;
    }

    /**
     * 获取条件查询的住房数据总数
     * @param community_id,query,view_power
     * @return int
     * @author 夏天
     * @date 2020/7/21 15:41
     */
    public int getCount(int community_id,String query,String view_power) throws  Exception{
        String sql = null;
        if (community_id==0&&query==null) {
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select count(*) as count from room  where Community_id in ("+view_power+") " ;
        }
        if(community_id > 0 && query==null){
            sql = "select count(*) as count from room  where  Community_id="+community_id+"  " ;

        }
        if(community_id >0 && query !=null){
            sql = "select count(*) as count from room join community on room.Community_id = community.Community_id where (Username LIKE '%"+query
                    +"%' or Ownername LIKE '%"+query+"%' or Housernum LIKE '%"+query+"%' or Phone LIKE '%"+query+"%' ) and room.Community_id="+community_id+"  " ;
        }
        if(community_id ==0 && query !=null){
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select count(*) as count from room join community on room.Community_id = community.Community_id where (Username LIKE '%"+query
                    +"%' or Ownername LIKE '%"+query+"%' or Housernum LIKE '%"+query+"%' or Phone LIKE '%"+query+"%' ) and room.Community_id in ("+view_power+" )  " ;
        }
        System.out.print("查询Count语句:"+sql );
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int i = rs.getInt("count");
        DButils.close(rs,st,con);
        return  i;
    }
}
